import pymysql

class StuOperation:
	"""
	stu表信息操作类
	方法：findall（）查询，del(id)删除，insert（data）添加
	"""

	#创建对象时执行如下
	def __init__(self):
		#建立数据库连接
		self.db = pymysql.connect(host='localhost',user='root',password='123456',db='test',charset='utf8')

		#建立游标对象
		self.cursor = self.db.cursor()

	#数据查询
	def findAll(self):
		try:
			self.cursor.execute("select * from stu")
			stulist = self.cursor.fetchall()
			print("="*16,"学员信息浏览","="*16)
			print("|{:^8s}|{:^10s}\t|{:^8s}|{:^10s}|".format("id","name","age","classid"))
			for student in stulist:
				id = student[0]
				name = student[1]
				age = student[2]
				classid = student[3]
				print("|{0:^8d}|{1:^9s}\t|{2:^8d}|{3:^10s}|".format(id,name,age,classid))
			print("="*45)
		except Exception as err:
			print("SQL执行失败，原因：",err)

	#数据删除
	def dele(self,id):
		try:
			self.cursor.execute('delete from stu where id=%d'%(id))
			self.db.commit()
			self.findAll()
		except Exception as err:
			print("SQL执行失败，原因：",err)

	#数据添加
	def insert(self,data):
		try:
			self.cursor.execute("insert into stu(name,age,classid) values('%s','%d','%s')"%(data['name'],data['age'],data['classid']))
			self.db.commit()
			self.findAll()
		except Exception as err:
			print("SQL执行失败，原因：",err)

	#关闭数据库连接
	def __del__(self):
		self.cursor.close()
		self.db.close()

student = StuOperation()

while True:
	print("="*16,"学员信息管理","="*16)
	print("{:6}{:15}{:15}".format("","1.查看学员信息","2.添加学员信息"))
	print("{:6}{:15}{:15}".format("","3.删除学员信息","4.退出系统"))
	print("="*45)
	key = input("请输入您的选择：")

	#查看学员信息
	if key == '1':
		student.findAll()
		input("按回车键继续：")

	#添加学员信息
	elif key == '2':
		data = {}
		# data['id'] = int(input("请输入要添加的学生id："))
		data['name'] = input("请输入要添加的学生姓名：")
		data['age'] = int(input("请输入要添加的学生年龄："))
		data['classid'] = input("请输入要添加的学生班级号：")
		student.insert(data)
		input("按回车键继续：")

	#删除学员信息
	elif key == '3':
		student.findAll()
		id = int(input("请输入要删除的学员id号："))
		student.dele(id)
		input("按回车键继续：")

	#退出系统
	elif key == '4':
		print("谢谢您的使用，再见！")
		break

	#异常提示
	else:
		print("****无效的键盘输入！****")

del student
